package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.*;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;

/**
 * Created by pjn on 2017/8/11.
 */
public class CoverRepositoryImpl {
    @PersistenceContext
    private EntityManager em;

    public List<CoverDto> getCover4DT(DTRequestDto dtRequestDto,
                                      String actorIds,
                                      Integer orgId,
                                      Boolean audit,
                                      String startRoad,
                                      String endRoad,
                                      String roadName) throws Exception {
        String sql = "select m.*, ur.UserName as coverCreatorName, " +
                "og1.OrgName as coverOrgName, og2.OrgName as coverMainOrgName " +
                "from tb_cover m, tb_user ur, tb_org og1, tb_org og2 " +
                "where ur.UserId = m.coverCreatorId " +
                "and og1.OrgId = m.coverOrgId and og2.OrgId = m.coverMainOrgId and " +
                "m.coverRoadName like '%" + roadName + "%' and m.coverStartRoad like '%" + startRoad + "%' " +
                "and m.coverEndRoad like '%" + endRoad + "%' and m.ProjectNumber like '%" + dtRequestDto.getSearch() + "%' ";
        if (audit)
            sql += "and coverIsComplete = 0 ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and m.coverMainOrgId=" + orgId + " ";
        else
            sql += "and m.coverOrgId = " + orgId + " ";
        if (startRoad != "" || endRoad != "") {
            sql += "union select m.*, ur.UserName as coverCreatorName, " +
                    "og1.OrgName as coverOrgName, og2.OrgName as coverMainOrgName " +
                    "from tb_cover m, tb_user ur, tb_org og1, tb_org og2 " +
                    "where ur.UserId = m.coverCreatorId " +
                    "and og1.OrgId = m.coverOrgId and og2.OrgId = m.coverMainOrgId and " +
                    "m.coverRoadName like '%" + roadName + "%' and m.coverStartRoad like '%" + endRoad + "%' " +
                    "and m.coverEndRoad like '%" + startRoad + "%' and m.ProjectNumber like '%" + dtRequestDto.getSearch() + "%' ";
            if (audit)
                sql += "and coverIsComplete = 0 ";
            if (actorIds.indexOf('1') >= 0)
                sql += "";
            else if (actorIds.indexOf('7') >= 0)
                sql += "and m.coverMainOrgId=" + orgId + " ";
            else
                sql += "and m.coverOrgId = " + orgId + " ";
        }
        sql += "order by coverInstallTime desc";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(CoverDto.class));
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
    }

    public Integer getCover4DTCount(DTRequestDto dtRequestDto,
                                    String actorIds,
                                    Integer orgId,
                                    Boolean audit,
                                    String startRoad,
                                    String endRoad,
                                    String roadName) throws Exception {
        String sql1 = "select count(*) from tb_cover m " +
                "where m.coverRoadName like '%" + roadName + "%' and m.coverStartRoad like '%" + startRoad + "%' " +
                "and m.coverEndRoad like '%" + endRoad + "%' and m.ProjectNumber like '%" + dtRequestDto.getSearch() + "%' ";
        if (audit)
            sql1 += "and coverIsComplete = 0 ";
        if (actorIds.indexOf('1') >= 0)
            sql1 += "";
        else if (actorIds.indexOf('7') >= 0)
            sql1 += "and m.coverMainOrgId=" + orgId + " ";
        else
            sql1 += "and m.coverOrgId = " + orgId + " ";

        Query query1 = em.createNativeQuery(sql1);
        Integer count1 = Integer.parseInt(query1.getSingleResult().toString());
        Integer count2 = 0;
        if (startRoad != "" || endRoad != "") {
            String sql2 = "select count(*) from tb_cover m " +
                    "where m.coverRoadName like '%" + roadName + "%' and m.coverStartRoad like '%" + endRoad + "%' " +
                    "and m.coverEndRoad like '%" + startRoad + "%' and m.ProjectNumber like '%" + dtRequestDto.getSearch() + "%' ";
            if (audit)
                sql2 += "and coverIsComplete = 0 ";
            if (actorIds.indexOf('1') >= 0)
                sql2 += "";
            else if (actorIds.indexOf('7') >= 0)
                sql2 += "and m.coverMainOrgId=" + orgId + " ";
            else
                sql2 += "and m.coverOrgId = " + orgId + " ";
            Query query2 = em.createNativeQuery(sql2);
            count2 = Integer.parseInt(query2.getSingleResult().toString());
        }
        return count2 + count1;
    }

    public List<String> getStartRoadByRoadName(String roadName, String actorIds, Integer orgId) throws Exception {
        String sql = "select  CoverStartRoad as roadName from tb_cover where CoverRoadName like '%" + roadName + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and coverMainOrgId=" + orgId + " ";
        else
            sql += "and coverOrgId = " + orgId + " ";
        sql += "union select CoverEndRoad as roadName from tb_cover where CoverRoadName like '%" + roadName + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and coverMainOrgId=" + orgId + " ";
        else
            sql += "and coverOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return query.getResultList();
    }

    public List<String> getEndRoadByStartRoad(String city, String coverRoadName, String startRoad, String actorIds, Integer orgId) throws Exception {
        String sql = "select CoverStartRoad as roadName from tb_cover where CoverRoadName like '%" + coverRoadName + "%'" +
                " and  CoverEndRoad like '%" + startRoad + "%' and CoverCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and coverMainOrgId=" + orgId + " ";
        else
            sql += "and coverOrgId = " + orgId + " ";
        sql += "union select CoverEndRoad as roadName from tb_cover where CoverRoadName like '%" + coverRoadName + "%'" +
                " and  CoverStartRoad like '%" + startRoad + "%' and CoverCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and coverMainOrgId=" + orgId + " ";
        else
            sql += "and coverOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return query.getResultList();
    }

    public List<CoverDto> getCover4APP(String actorIds, Integer orgId, Integer mCount, Integer mSize, String city, String startRoad, String endRoad, String roadName) throws Exception {
        String sql = "select m.*, ur.UserName as coverCreatorName, " +
                "og1.OrgName as coverOrgName, og2.OrgName as coverMainOrgName " +
                "from tb_cover m, tb_user ur, tb_org og1, tb_org og2 " +
                "where ur.UserId = m.CoverCreatorId " +
                "and og1.OrgId = m.CoverOrgId and og2.OrgId = m.CoverMainOrgId " +
                "and m.CoverStartRoad like '%" + startRoad + "%' and m.CoverEndRoad like '%" + endRoad + "%' " +
                "and m.CoverRoadName like '%" + roadName + "%' and m.CoverCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and m.CoverMainOrgId=" + orgId + " ";
        else
            sql += "and m.CoverOrgId = " + orgId + " ";
        if (startRoad != "" || endRoad != "") {
            sql += "union select m.*, ur.UserName as coverCreatorName," +
                    "og1.OrgName as coverOrgName, og2.OrgName as coverMainOrgName " +
                    "from tb_cover m, tb_user ur, tb_org og1, tb_org og2 " +
                    "where ur.UserId = m.CoverCreatorId " +
                    "and og1.OrgId = m.CoverOrgId and og2.OrgId = m.CoverMainOrgId " +
                    "and m.CoverStartRoad like '%" + endRoad + "%' and m.CoverEndRoad like '%" + startRoad + "%' " +
                    "and m.CoverRoadName like '%" + roadName + "%' and m.CoverCity like '%" + city + "%' ";
            if (actorIds.indexOf('1') >= 0)
                sql += "";
            else if (actorIds.indexOf('7') >= 0)
                sql += "and m.CoverMainOrgId=" + orgId + " ";
            else
                sql += "and m.CoverOrgId = " + orgId + " ";
        }
        sql += "order by concat(CoverInstallTime, coverId) desc";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(CoverDto.class));
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

    public List<CoverDto> getCoverById(Integer coverId) throws Exception {
        String sql = "select se.* ,o1.orgName as coverOrgName,o2.OrgName as coverMainOrgName," +
                " u.userName as coverCreatorName from tb_cover se,tb_org o1,tb_user u,tb_org o2 " +
                "where o1.orgId=se.coverOrgId and o2.orgId=se.coverMainOrgId and u.UserId=se.coverCreatorId" +
                " and se.coverId=" + coverId + " ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(CoverDto.class));
        return query.getResultList();
    }

    public List<String> getCity4Cover4App(String actorIds, Integer orgId, Integer mCount, Integer mSize) throws Exception {
        String sql = "select DISTINCT CoverCity from tb_cover ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += " where CoverMainOrgId=" + orgId + " ";
        else
            sql += " where CoverOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

    public List<String> getRoadName4CoverByCity(String actorIds, Integer orgId, String city, Integer mCount, Integer mSize) throws Exception {
        String sql = "select DISTINCT CoverRoadName from  tb_cover where CoverCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += " and CoverMainOrgId=" + orgId + " ";
        else
            sql += " and CoverOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

    public List<String> getEndRoadByStartRoad4App(String city, String coverRoadName, String startRoad, String actorIds, Integer orgId, Integer mCount, Integer mSize) {
        String sql = "select CoverStartRoad as roadName from tb_cover where CoverRoadName like '%" + coverRoadName + "%'" +
                " and  CoverEndRoad like '%" + startRoad + "%' and CoverCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and coverMainOrgId=" + orgId + " ";
        else
            sql += "and coverOrgId = " + orgId + " ";
        sql += "union select CoverEndRoad as roadName from tb_cover where CoverRoadName like '%" + coverRoadName + "%'" +
                " and  CoverStartRoad like '%" + startRoad + "%' and CoverCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and coverMainOrgId=" + orgId + " ";
        else
            sql += "and coverOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

    public List<String> getStartRoadByRoadName4App(String city, String roadName, String actorIds, Integer orgId, Integer mCount, Integer mSize) throws Exception {
        String sql = "select  CoverStartRoad as roadName from tb_cover where CoverRoadName like '%" + roadName + "%' " +
                "and CoverCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and coverMainOrgId=" + orgId + " ";
        else
            sql += "and coverOrgId = " + orgId + " ";
        sql += "union select CoverEndRoad as roadName from tb_cover where CoverRoadName like '%" + roadName + "%' " +
                "and CoverCity like '%" + city + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and coverMainOrgId=" + orgId + " ";
        else
            sql += "and coverOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

    public List<CoverDto> getCover4Map4App(Integer orgId,
                                           String actorIds,
                                           double neLongitude,
                                           double neLatitude,
                                           double swLongitude,
                                           double swLatitude) throws Exception {
        String sql = "select * from tb_cover ";
        sql += "where coverIsComplete = 1 " +
                "and coverLongitude < :neLongitude and coverLongitude > :swLongitude " +
                "and coverLatitude < :neLatitude and coverLatitude > :swLatitude ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and coverMainOrgId=" + orgId + " ";
        else
            sql += "and coverOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        query.setParameter("neLongitude", String.valueOf(neLongitude));
        query.setParameter("swLongitude", String.valueOf(swLongitude));
        query.setParameter("neLatitude", String.valueOf(neLatitude));
        query.setParameter("swLatitude", String.valueOf(swLatitude));
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(CoverDto.class));
        return query.getResultList();
    }

    public List<CoverFaultDto> getCoverCoordinate4Map(Integer orgId, Integer actorId) throws Exception {
        String sql = "SELECT c.CoverId,c.CoverLongitude,c.CoverLatitude,c.CoverPhoto\n" +
                "FROM tb_cover c WHERE CoverIsComplete=1 ";
        if (actorId != 1) {
            sql += " and  c.CoverOrgId=" + orgId + " ";
        }
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(CoverFaultDto.class));
        return query.getResultList();
    }

    public Integer getCoverFaultReportCount4DT(String search, Integer actorId, Integer orgId, String coverRoadName, String coverStartRoad, String coverEndRoad) throws Exception {
        String sql = "select count(*) " +
                "FROM ((tb_cover c JOIN tb_org o ON o.OrgId=c.CoverOrgId)\n" +
                "JOIN tb_user u ON u.UserId=c.CoverCreatorId)\n" +
                "WHERE c.CoverRoadName LIKE '%" + coverRoadName + "%' AND c.CoverStartRoad LIKE '%" + coverStartRoad + "%' AND c.CoverEndRoad LIKE '%" + coverEndRoad + "%'";
        if (actorId != 1) {
            sql += " and c.CoverOrgId=" + orgId + "";
        }
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public List<DTCoverFaultDto> getCoverFaultReport4DT(DTRequestDto dtRequestDto, Integer actorId, Integer orgId, String coverRoadName, String coverStartRoad, String coverEndRoad) throws Exception {
        String sql = "SELECT c.CoverId,c.CoverPhoto,c.CoverProvince,c.CoverCity," +
                "c.CoverArea,c.CoverRegionId,o.OrgName AS coverOrgName,u.UserName AS coverCreatorName," +
                "c.CoverInstallTime\n" +
                "FROM ((tb_cover c JOIN tb_org o ON o.OrgId=c.CoverOrgId)\n" +
                "JOIN tb_user u ON u.UserId=c.CoverCreatorId)\n" +
                "WHERE c.CoverRoadName LIKE '%" + coverRoadName + "%' AND c.CoverStartRoad LIKE '%" + coverStartRoad + "%' AND c.CoverEndRoad LIKE '%" + coverEndRoad + "%'";
        if (actorId != 1) {
            sql += " and c.CoverOrgId=" + orgId + "";
        }
        if (coverStartRoad != "" || coverEndRoad != "") {
            sql += " UNION SELECT c.CoverId,c.CoverPhoto,c.CoverProvince,c.CoverCity," +
                    "c.CoverArea,c.CoverRegionId,o.OrgName AS coverOrgName,u.UserName AS coverCreatorName," +
                    "c.CoverInstallTime\n" +
                    "FROM ((tb_cover c JOIN tb_org o ON o.OrgId=c.CoverOrgId)\n" +
                    "JOIN tb_user u ON u.UserId=c.CoverCreatorId)\n" +
                    "WHERE c.CoverRoadName LIKE '%" + coverRoadName + "%' AND c.CoverStartRoad LIKE '%" + coverEndRoad + "%' AND c.CoverEndRoad LIKE '%" + coverStartRoad + "%'";
        }
        if (actorId != 1) {
            sql += " and c.CoverOrgId=" + orgId + "";
        }
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(DTCoverFaultDto.class));
        List<DTCoverFaultDto> coverFaultDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return coverFaultDtos;
    }

    public List<String> getStartRoad(String roadName, Integer actorId, Integer orgId) throws Exception {
        String sql = "SELECT c.CoverStartRoad AS coverStartRoad\n" +
                "FROM tb_cover c " +
                "WHERE c.CoverRoadName LIKE '%" + roadName + "%'";
        if (actorId != 1) {
            if (actorId == 7)
                sql += " and c.CoverMainOrgId=" + orgId + " ";
            else
                sql += " and c.CoverOrgId=" + orgId + " ";
        }
        sql += " UNION SELECT c.CoverEndRoad AS coverStartRoad\n" +
                "FROM tb_cover c " +
                "WHERE c.CoverRoadName LIKE '%" + roadName + "%'";
        if (actorId != 1) {
            if (actorId == 7)
                sql += " and c.CoverMainOrgId=" + orgId + " ";
            else
                sql += " and c.CoverOrgId=" + orgId + " ";
        }
        Query query = em.createNativeQuery(sql);
        return query.getResultList();
    }

    public List<String> getSectionByStartRoad(String roadName, Integer actorId, Integer orgId) throws Exception {
        String sql = "SELECT c.CoverStartRoad AS coverStartRoad\n" +
                "FROM tb_cover c\n" +
                "WHERE c.CoverEndRoad LIKE '%" + roadName + "%'";
        if (actorId != 1) {
            if (actorId == 7)
                sql += " and c.CoverMainOrgId=" + orgId + " ";
            else
                sql += " and c.CoverOrgId=" + orgId + " ";
        }
        sql += " UNION SELECT c.CoverEndRoad AS coverStartRoad\n" +
                "FROM tb_cover c\n" +
                "WHERE c.CoverStartRoad LIKE '%" + roadName + "%'";
        if (actorId != 1) {
            if (actorId == 7)
                sql += " and c.CoverMainOrgId=" + orgId + " ";
            else
                sql += " and c.CoverOrgId=" + orgId + " ";
        }
        Query query = em.createNativeQuery(sql);
        return query.getResultList();
    }

    public List<CoverDto> getCoverCoordinates4Map(Integer orgId, Integer orgType, Integer actorId) throws Exception {
        String sql = "select CoverId, CoverLongitude as longitude , CoverLatitude as latitude from tb_cover ";
        if (actorId != 1) {
            if (orgType == 0)
                sql += " where coverOrgId=" + orgId + " ";
            else if (orgType == 1)
                sql += " where coverMainOrgId=" + orgId + " ";
        }
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(CoverDto.class));
        return query.getResultList();
    }

    public Integer getCoverCount(String actorIds, Integer orgId)throws Exception{
        String sql="select count(*) from tb_cover ";
        if(!actorIds.contains("1")){
            sql+=" where CoverOrgId="+orgId+"";
        }
        Query query=em.createNativeQuery(sql);
        return (Integer) query.getSingleResult();
    }
}
